﻿using MgSoft.Excel.Model;
using MgSoft.Excel.Model.Dto;
using MgSoft.Excel.Model.Excel;
using MgSoft.Excel.Model.Template;
using MgSoft.Excel.Util;
using Newtonsoft.Json;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Security.Cryptography.X509Certificates;
using System.Text;
using System.Threading.Tasks;

namespace MgSoft.Excel
{
    /// <summary>
    /// Mgsoft处理Excel通用类
    /// </summary>
    public class MgExcel
    {

        /// <summary>
        /// Excel文件路径
        /// </summary>
        public string FilePath { get; private set; }

        /// <summary>
        /// Excel文件流
        /// </summary>
        public Stream FileStream { get; private set; }

        /// <summary>
        /// Excel文件类型
        /// </summary>
        public ExcelType ExcelType { get; private set; }

        /// <summary>
        /// 模板单配置
        /// </summary>
        public TemplateConfig TemplateConfig { get; private set; }

        /// <summary>
        /// 转换后的Excel表格
        /// </summary>
        public MgSheet MgSheet { get; private set; }

        private IWorkbook Workbook { get; set; }

        private readonly static object iLock = new object();

        #region read

        /// <summary>
        /// 读取Excel
        /// </summary>
        /// <param name="fileStream">Excel文件流</param>
        /// <param name="excelType">Excel文件类型</param>
        /// <param name="templateConfig">模板</param>
        /// <returns></returns>
        public async Task<List<ExcelErrorMessage>> ReadAsync(Stream fileStream, ExcelType excelType, TemplateConfig templateConfig) => await ReadAsync(fileStream: fileStream, excelType: excelType, templateConfig: templateConfig);

        /// <summary>
        /// 读取Excel
        /// </summary>
        /// <param name="fileStream">Excel文件流</param>
        /// <param name="excelType">Excel文件类型</param>
        /// <param name="templateConfigJson">模板json</param>
        /// <returns></returns>
        public async Task<List<ExcelErrorMessage>> ReadAsync(Stream fileStream, ExcelType excelType, String templateConfigJson) => await ReadAsync(fileStream: fileStream, excelType: excelType, templateConfigJson: templateConfigJson);

        /// <summary>
        /// 读取Excel
        /// </summary>
        /// <param name="fileStream">Excel文件流</param>
        /// <param name="excelType">Excel文件类型</param>
        /// <param name="templateConfigJsonFilePath">模板json文件路径</param>
        /// <param name="isTemplateConfigJsonFile">是否模板json文件路径（主要作用重载）</param>
        /// <returns></returns>
        public async Task<List<ExcelErrorMessage>> ReadAsync(Stream fileStream, ExcelType excelType, String templateConfigJsonFilePath, bool isTemplateConfigJsonFile) => await ReadAsync(fileStream: fileStream, excelType: excelType, templateConfigJsonFilePath: templateConfigJsonFilePath);

        /// <summary>
        /// 读取Excel
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <param name="templateConfigJson">模板json</param>
        /// <returns></returns>
        public async Task<List<ExcelErrorMessage>> ReadAsync(string filePath, String templateConfigJson) => await ReadAsync(filePath: filePath, templateConfigJson: templateConfigJson);

        /// <summary>
        /// 读取Excel
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <param name="templateConfig">模板</param>
        /// <returns></returns>
        public async Task<List<ExcelErrorMessage>> ReadAsync(string filePath, TemplateConfig templateConfig) => await ReadAsync(fileStream: null, filePath: filePath, templateConfig: templateConfig);

        private async Task<List<ExcelErrorMessage>> ReadAsync(Stream fileStream = null, string filePath = null, ExcelType excelType = ExcelType.xlsx, TemplateConfig templateConfig = null, String templateConfigJson = null, string templateConfigJsonFilePath = null)
        {
            List<ExcelErrorMessage> result = new List<ExcelErrorMessage>();
            try
            {
                await Initialize(fileStream, filePath, excelType, templateConfig, templateConfigJson, templateConfigJsonFilePath);

                InitCheck();
                InitSheetByNopi();
                InitMgSheetByNopi(result);
            }
            catch (MgExcelException ex)
            {
                result.Add(new ExcelErrorMessage(ex.Message, ex.StackTrace));
            }

            return result;
        }


        public async Task<List<ExcelErrorMessage>> ReadOledb(string filePath, TemplateConfig templateConfig)
        {
            await Initialize(filePath: filePath, templateConfig: templateConfig);
            List<ExcelErrorMessage> result = new List<ExcelErrorMessage>();
            try
            {
                InitCheck();
                var dateTable = InitTableByOledb();
                InitMgSheetByOledb(result, dateTable);
            }
            catch (MgExcelException ex)
            {
                result.Add(new ExcelErrorMessage(ex.Message, ex.StackTrace));
            }

            return result;
        }

        private void InitMgSheetByOledb(List<ExcelErrorMessage> errorMessages, DataTable dateTable)
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();
            MgSheet = MgSheetOledbUtil.ConvertToMgSheet(dateTable, TemplateConfig, errorMessages);
            stopwatch.Stop();
            System.Diagnostics.Debug.WriteLine($"转换MgSheet用时:{stopwatch.ElapsedMilliseconds / 1000}秒");
        }

        private DataTable InitTableByOledb()
        {
            return ExcelOledbUtil.ReadExcelToTable(FilePath);
        }
        #endregion


        /// <summary>
        ///  把错误信息写入Excel表
        /// </summary>
        /// <param name="folderPath">保存新Excel表的文件夹</param>
        /// <param name="excelErrorMessages"></param>
        public void WriteErrorToNewExcel(string folderPath, List<ExcelErrorMessage> excelErrorMessages)
        {
            if (excelErrorMessages == null || excelErrorMessages.Count <= 0) throw new Exception("错误信息集数据源为空");
            if (string.IsNullOrEmpty(TemplateConfig.ErrorFlagColumnName) || string.IsNullOrEmpty(TemplateConfig.ErrorMessageColumnName)) throw new Exception("TemplateConfigService没有配置错误信息的写入列");
            var mgCellList = new List<MgCell>();

            var groupExcelErrorMessages = excelErrorMessages.GroupBy(g => g.RowIndex)
                .Select(s =>
                new ExcelErrorMessage
                {
                    RowIndex = s.Key,
                    ColumnIndex = s.FirstOrDefault().ColumnIndex,
                    ColumnName = s.FirstOrDefault().ColumnName,
                    Detailed = s.FirstOrDefault().Detailed,
                    Message = string.Join("\r\n", s.Select(ss => ss.Message).ToList())
                }).ToList();

            foreach (var item in groupExcelErrorMessages)
            {
                if (item.RowIndex != null)
                {
                    var tempFlagCell = new MgCell()
                    {
                        RowIndex = item.RowIndex ?? 0,
                        ColumnIndex = SheetColumnNameUtil.ColumnNameToColumnIndex(TemplateConfig.ErrorFlagColumnName),
                        Value = "error"
                    };
                    mgCellList.Add(tempFlagCell);
                    var tempMessageCell = new MgCell()
                    {
                        RowIndex = item.RowIndex ?? 0,
                        ColumnIndex = SheetColumnNameUtil.ColumnNameToColumnIndex(TemplateConfig.ErrorMessageColumnName),
                        Value = item.Message
                    };
                    mgCellList.Add(tempMessageCell);
                }
            }
            if (mgCellList.Count <= 0) throw new Exception("构建错误信息集失败");

            //var tempFileName = Path.GetFileName(this.FilePath).Replace('.', '');
            var newPath = $@"{folderPath}\{Path.GetFileNameWithoutExtension(this.FilePath)}{DateTime.Now.ToString("yyyyMMddHHmmss")}{Path.GetExtension(this.FilePath)}";
            ExceltUtil.WriteToNewExcel(this.FilePath, newPath, this.ExcelType, TemplateConfig.SheetIndex, mgCellList);
        }
        /// <summary>
        /// 将读取Excel数据映射到实体对象
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <returns></returns>
        public async Task<List<T>> ToListAsync<T>() where T : IExcelDto, new()
        {
            return await Task.Run<List<T>>(() =>
            {
                return ToList<T>();

            });

        }


        /// <summary>
        /// 将读取Excel数据映射到实体对象
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <returns></returns>
        public List<T> ToList<T>() where T : IExcelDto, new()
        {
            List<T> lists = new List<T>(this.MgSheet.MaxRowIndex);

            Parallel.For(0, this.MgSheet.MaxRowIndex, i =>
            {
                var row = this.MgSheet.GetRow(i + 1);
                addList(lists, row);
            });
            return lists;


        }

        private void addList<T>(List<T> lists, MgRow row) where T : IExcelDto, new()
        {
            lock (iLock)
            {
                lists.Add(row.ToObject<T>());
            }
        }


        private async Task Initialize(Stream fileStream = null, string filePath = null, ExcelType excelType = ExcelType.xlsx, TemplateConfig templateConfig = null, String templateConfigJson = null, string templateConfigJsonFilePath = null)
        {
            if (fileStream != null)
            {
                this.FileStream = new MemoryStream(); //此处应该拷贝
                await fileStream?.CopyToAsync(FileStream);
                fileStream.Dispose();
            }


            this.FilePath = filePath;

            this.ExcelType = excelType;
            if (!String.IsNullOrEmpty(this.FilePath))
                this.ExcelType = FilePath.EndsWith(".xls") ? ExcelType.xls : ExcelType.xlsx;


            this.TemplateConfig = templateConfig;
            if (!String.IsNullOrEmpty(templateConfigJson))
                this.TemplateConfig = JsonConvert.DeserializeObject<TemplateConfig>(templateConfigJson);

            if (!String.IsNullOrEmpty(templateConfigJsonFilePath))
            {
                StreamReader sr = new StreamReader(templateConfigJsonFilePath);
                var jsonSrt = sr.ReadToEnd();
                this.FilePath = filePath;
                this.TemplateConfig = JsonConvert.DeserializeObject<TemplateConfig>(jsonSrt);
            }
        }

        private void InitMgSheetByNopi(List<ExcelErrorMessage> errorMessages)
        {
            var sheet = Workbook.GetSheetAt(TemplateConfig.SheetIndex);
            MgSheet = MgSheetUtil.ConvertToMgSheet(sheet, TemplateConfig, errorMessages);
        }

        private void InitCheck()
        {
            if (TemplateConfig == null)
            {
                throw new MgExcelException("请选择数据模板");
            }
        }

        private void InitSheetByNopi()
        {
            Workbook = String.IsNullOrEmpty(FilePath) ? ExceltUtil.ReadFromExcelStream(FileStream, ExcelType) : ExceltUtil.ReadFromExcelFile(FilePath, this.ExcelType);
        }
    }
}
